package jude.java.util.parse;

import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.Date;

import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.util.CellReference;

public class ExcelParser {

	private static final String DATE_FORMAT = "yyyy-MM-dd";
	
/**
 * parse sample code
 * 
	public void testExcel() throws IOException {
		POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream("D:\\a.xls"));
		HSSFWorkbook wb = new HSSFWorkbook(fs);
		HSSFSheet sheet = wb.getSheetAt(0);
		int count = sheet.getLastRowNum();
		for (int i=0; i<count; i++) {
			String cellName = "A" + (i + 1);
			String value = ExcelParser.getCellValue(sheet, cellName);
			if (StringUtils.isNotEmpty(value)) {
				System.out.println(value);
			}
		}
	}
*/
	
	/** 获取单元格值 */
	public static String getCellValue(HSSFSheet sheet, String cellName) {
		if (StringUtils.isEmpty(cellName))
			return null;
		CellReference cellReference = new CellReference(cellName);
		HSSFRow row = sheet.getRow(cellReference.getRow());
		if (row == null)
			return null;
		HSSFCell cell = row.getCell(cellReference.getCol() & 0x0000FFFF);
		cell.getCellStyle();

		switch (cell.getCellType()) {
		case HSSFCell.CELL_TYPE_BOOLEAN:
			return String.valueOf(cell.getBooleanCellValue());
		case HSSFCell.CELL_TYPE_NUMERIC:
			if (HSSFDateUtil.isCellDateFormatted(cell)) {
				Date d = cell.getDateCellValue();
				if (d != null) {
					SimpleDateFormat format = new SimpleDateFormat(DATE_FORMAT);
					return format.format(d);
				}
			}
			return getNumberString(cell.getNumericCellValue());
		case HSSFCell.CELL_TYPE_STRING:
			return String.valueOf(cell.getRichStringCellValue());
		case HSSFCell.CELL_TYPE_BLANK:
			return "";
		case HSSFCell.CELL_TYPE_ERROR:
			break;
		case HSSFCell.CELL_TYPE_FORMULA:
			break;
		}
		return null;
	}
	
	public static String getNumberString(double d) {
		String s = String.valueOf(d);
		if(s.indexOf('E') == -1)
			return s;
		BigDecimal bd = new BigDecimal(s);
		return bd.toString();
	}
	
}
